﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace trabalhoCsharp
{
    class Program
    {
        static void clearFile()
        {
            TextWriter tw;

            try
            {
                tw = new StreamWriter("index.html");

                string text = "";

                tw.Write(text);

                tw.Close();
            }
            catch (FileNotFoundException) { }
        }

        static void writeFile(string text)
        {
            TextReader tr;
            TextWriter tw;

            try
            {
                tr = new StreamReader("index.html");
            }
            catch (FileNotFoundException)
            {
                StreamWriter r = File.CreateText("index.html");
                r.Close();

                tw = new StreamWriter("index.html");

                tw.WriteLine("<html>");

                tw.Close();

                tr = new StreamReader("index.html");
            }

            string text1 = tr.ReadToEnd();

            tr.Close();

            tw = new StreamWriter("index.html");

            tw.WriteLine(text1);

            tw.WriteLine("<p>" + text + "</p>");

            tw.Close();
        }

        static DataTable connectDB(string commandSql)
        {
            SQLiteConnection cnn = new SQLiteConnection("Data Source=pristine.db");

            cnn.Open();

            SQLiteCommand mycommand = new SQLiteCommand(cnn);

            mycommand.CommandText = commandSql;

            SQLiteDataReader reader = mycommand.ExecuteReader();
            
            DataTable dt = new DataTable();

            dt.Load(reader);

            reader.Close();
                
            cnn.Close();

            return dt;
        }

        static void print_ListTeachers(List<string> list)
        {
            List<string> listTeachers = new List<string>();

            foreach (string number in list)
            {
                String Sql = "SELECT nome FROM informatica_docente WHERE id= '" + number + "'";
                
                listTeachers.Add(connectDB_getList(Sql, "nome")[0]);
            }

            listTeachers.Sort(delegate(string A, string B)
            {
                return A.CompareTo(B);
            });

            string text = "";

            foreach (string teacher in listTeachers)
            {
                text += " &nbsp; " + teacher + "<p>\n";
            }

            writeFile(text);
        }

        static List<string> connectDB_getList(string commandSql , string column)
        {
            SQLiteConnection cnn = new SQLiteConnection("Data Source=pristine.db");

            cnn.Open();

            SQLiteCommand mycommand = new SQLiteCommand(cnn);

            mycommand.CommandText = commandSql;

            SQLiteDataReader reader = mycommand.ExecuteReader();

            List<string> list = new List<string>();

            while (reader.Read())
            {
                list.Add(reader[0].ToString());
            }

            reader.Close();

            cnn.Close();

            return list;
        }

        static Dictionary<string, string> connectDB_getDictionary(string commandSql, string column1, string column2)
        {
            SQLiteConnection cnn = new SQLiteConnection("Data Source=pristine.db");

            cnn.Open();

            SQLiteCommand mycommand = new SQLiteCommand(cnn);

            mycommand.CommandText = commandSql;

            SQLiteDataReader reader = mycommand.ExecuteReader();

            Dictionary<string, string> dict = new Dictionary<string, string>();

            while (reader.Read())
            {
                if (dict.ContainsKey(reader[column1].ToString()) == false)
                {
                    dict.Add(reader[column1].ToString(), reader[column2].ToString());
                }
            }

            reader.Close();

            cnn.Close();

            return dict;
        }

        static void count_teachers_that_changed_category_year(int iAno, string iEstab)
        {
            if (iAno >= 1 && iAno <= 9)
            {
                String commandSql = "SELECT DISTINCT docente_id, categoria_id FROM informatica_registodocencia WHERE ano= '"
                                            + iAno + "'";

                Dictionary<string, string> dict1 = connectDB_getDictionary(commandSql, "docente_id", "categoria_id");


                String commandSql1 = "SELECT DISTINCT docente_id, categoria_id FROM informatica_registodocencia WHERE ano= '"
                                        + (iAno - 1) + "'";

                Dictionary<string, string> dict2 = connectDB_getDictionary(commandSql1, "docente_id", "categoria_id");

                int countTeachers = 0;

                foreach (KeyValuePair<string, string> pair in dict1)
                {
                    if (dict2.ContainsKey(pair.Key))
                    {
                        if (dict1[pair.Key].CompareTo(dict2[pair.Key]) != 0)
                        {
                            countTeachers++;
                        }
                    }
                }

                writeFile("Number of teachers promoted to the next category from the year 200" + (iAno - 1)
                            + " to year 200" + iAno + ": " + countTeachers);
            }
            else { Console.WriteLine("The year you indicated is invalid | Please enter one from 2001 to 2009"); }
        }

        static void list_holders_year_degree(int iAno, string degree)
        {
            String commandSql1 = "SELECT id FROM informatica_grau WHERE ciclo_estudos_id= " +
                                "(SELECT ciclo_estudos_id FROM informatica_grau " +
                                "WHERE designacao= '" + degree + "')";

            List<string> listDegree = connectDB_getList(commandSql1, "id");

            List<string> listTeacher = new List<string>();

            List<string> listTeachers = new List<string>();

            foreach (string degree1 in listDegree)
            {
                String commandSql = "SELECT docente_id " +
                                        "FROM informatica_registodocencia WHERE ano= " +
                                        iAno + " AND grau_id= '" + degree1 + "'";

                listTeacher.AddRange(connectDB_getList(commandSql, "docente_id"));
            }

            foreach (string teacher in listTeacher)
            {
                if (listTeachers.Contains(teacher) == false)
                {
                    listTeachers.Add(teacher);
                }
            }

            writeFile("Holders of a " + degree + " degree in year 200" + iAno + "<p>\n" +
                      " &nbsp; The number of holders: " + listTeachers.Count + "<p>\n" +
                      " &nbsp; The set of holders: ");

            print_ListTeachers(listTeachers);
        }

        static void list_teachers_that_changed_establishment_year(int iAno)
        {
            if (iAno >= 1 && iAno <= 9)
            {
                String commandSql = "SELECT DISTINCT id, nome FROM informatica_docente WHERE ano BETWEEN '"
                                            + (iAno - 1) + "' AND '" + iAno + "'";

                Dictionary<string, string> ListAno = connectDB_getDictionary(commandSql, "id", "nome");


                String commandSql1 = "SELECT DISTINCT id, designacao FROM informatica_estabelecimento WHERE ano BETWEEN '"
                                        + (iAno - 1) + "' AND '" + iAno + "'";

                Dictionary<string, string> ListAno3 = connectDB_getDictionary(commandSql1, "id", "designacao");


                String commandSql2 = "SELECT DISTINCT docente_id, estabelecimento_id FROM informatica_registodocencia WHERE ano= '"
                                        + iAno + "'";

                DataTable data1 = connectDB(commandSql2);

                SortedDictionary<string, string> ListAno1 = new SortedDictionary<string, string>();

                foreach (DataRow row in data1.Rows)
                {
                    if (ListAno1.ContainsKey(ListAno[row[0].ToString()]) == false)
                    {

                        ListAno1.Add(ListAno[row[0].ToString()], ListAno3[row[1].ToString()]);
                    }
                }

                String commandSql3 = "SELECT DISTINCT docente_id, estabelecimento_id FROM informatica_registodocencia WHERE ano= '"
                                        + (iAno - 1) + "'";

                DataTable data2 = connectDB(commandSql3);

                Dictionary<string, string> ListAno2 = new Dictionary<string, string>();

                foreach (DataRow row in data2.Rows)
                {
                    if (ListAno2.ContainsKey(ListAno[row[0].ToString()]) == false)
                    {
                        ListAno2.Add(ListAno[row[0].ToString()], ListAno3[row[1].ToString()]);
                    }
                }

                string teachers = "";

                int countTeachers = 0;

                foreach (KeyValuePair<string, string> pair in ListAno1)
                {
                    if (ListAno2.ContainsKey(pair.Key))
                    {
                        if (ListAno1[pair.Key].CompareTo(ListAno2[pair.Key]) != 0)
                        {
                            countTeachers++;

                            teachers += "    " + pair.Key + " &nbsp; currently in: "
                                + ListAno1[pair.Key] + " &nbsp; came from: " + ListAno2[pair.Key] + "<p>\n";
                        }
                    }
                }

                writeFile("Number of teachers that changed establishment from the year 200" + (iAno - 1)
                            + " to year 200" + iAno + ": " + countTeachers);
                writeFile("Set of teachers that changed establishment from the year 200" + (iAno - 1) + " to year 200" + iAno);
                writeFile(teachers);
            }
            else { Console.WriteLine("The year you indicated is invalid | Please enter one from 2001 to 2009"); }
        }

        static List<string> personnel_leaving(int iAno, string iEstab)
        {
            String commandSql1 = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano= '"
                                    + iAno + "' AND estabelecimento_id= " +
                                    "(SELECT id FROM informatica_estabelecimento WHERE designacao= '"
                                    + iEstab + "' AND ano= '" + iAno + "')";

            List<string> listEstab1 = connectDB_getList(commandSql1, "docente_id");

            String commandSql2 = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano= '"
                                    + iAno + 1 + "' AND estabelecimento_id= " +
                                    "(SELECT id FROM informatica_estabelecimento WHERE designacao= '"
                                    + iEstab + "' AND ano= '" + iAno + 1 + "')";

            List<string> listEstab2 = connectDB_getList(commandSql2, "docente_id");

            List<string> listEstabLeaving = new List<string>();

            foreach (string item in listEstab1)
            {
                if (listEstab2.Contains(item) == false)
                {
                    listEstabLeaving.Add(item);
                }
            }

            writeFile("Personnel leaving the institution " + iEstab + " at the end of 200" + iAno + "<p>\n" +
                      " &nbsp; Number of personnel leaving: " + listEstabLeaving.Count + "<p>\n" +
                      " &nbsp; List of personnel leaving: ");

            return listEstabLeaving;
        }

        static List<string> personnel_arriving(int iAno, string iEstab)
        {
            String commandSql1 = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano= '"
                                    + iAno + "' AND estabelecimento_id= " +
                                    "(SELECT id FROM informatica_estabelecimento WHERE designacao= '"
                                    + iEstab + "' AND ano= '" + iAno + "')";

            List<string> listEstab1 = connectDB_getList(commandSql1, "docente_id");

            String commandSql2 = "SELECT DISTINCT docente_id FROM informatica_registodocencia WHERE ano= '"
                                    + (iAno - 1) + "' AND estabelecimento_id= " +
                                    "(SELECT id FROM informatica_estabelecimento WHERE designacao= '"
                                    + iEstab + "' AND ano= '" + (iAno - 1) + "')";

            List<string> listEstab2 = connectDB_getList(commandSql2, "docente_id");

            List<string> listEstabArriving = new List<string>();

            foreach (string item in listEstab1)
            {
                if (listEstab2.Contains(item) == false)
                {
                    listEstabArriving.Add(item);
                }
            }

            writeFile("Personnel arriving the institution " + iEstab + " at the begin of 200" + iAno + "<p>\n" +
                      " &nbsp;Number of personnel arriving: " + listEstabArriving.Count + "<p>\n" +
                      " &nbsp;List of personnel arriving: ");

            return listEstabArriving;
        }

        static void personnel_leaving_arriving_institution_year(int iAno, string iEstab)
        {
            if (iAno == 0)
            {
                writeFile("Just the personnel leaving because there is no previous year to compare");

                List<string> listEstabLeaving = personnel_leaving(iAno, iEstab);
                print_ListTeachers(listEstabLeaving);
            }
            else if (iAno > 0 && iAno < 9)
            {
                List<string> listEstabArriving = personnel_arriving(iAno, iEstab);
                print_ListTeachers(listEstabArriving);

                List<string> listEstabLeaving = personnel_leaving(iAno, iEstab);
                print_ListTeachers(listEstabLeaving);
            }
            else if (iAno == 9)
            {
                writeFile("Just the personnel arriving because there is no next year to compare");

                List<string> listEstabArriving = personnel_arriving(iAno, iEstab);
                print_ListTeachers(listEstabArriving);
            }
        }

        static void list_establishments_year(int iAno)
        {
            String commandSql = "SELECT designacao FROM informatica_estabelecimento WHERE ano= '" + iAno + "'";

            List<string> listEstab = connectDB_getList(commandSql, "designacao");

            listEstab.Sort(delegate(string A, string B)
            {
                return A.CompareTo(B);
            });

            string text = "";

            foreach (string establishment in listEstab)
            {
                text += " &nbsp;&nbsp; " + establishment + "<p>\n";
            }

            writeFile("Establishments list: <p>\n" +
                      " &nbsp; Total of establishments: " + listEstab.Count);

            writeFile(text);
        }

        static void list_teachers_doctorateDegree_year_estab(int iAno, string iEstab)
        {
            string degree = "Doutoramento";

            String commandSql1 = "SELECT id FROM informatica_grau WHERE ciclo_estudos_id= " +
                                "(SELECT ciclo_estudos_id FROM informatica_grau " +
                                "WHERE designacao= '" + degree + "')";

            List<string> listDegree = connectDB_getList(commandSql1, "id");

            DataTable dt = new DataTable();

            List<string> listTeachers = new List<string>();

            List<string> listTeacher = new List<string>();

            String commandSql2 = "SELECT id FROM informatica_estabelecimento WHERE designacao= '" +
                                 iEstab + "' AND ano= '" + iAno + "'";

            String idEstab = connectDB_getList(commandSql2, "id")[0];

            foreach (string degree1 in listDegree)
            {
                String commandSql = "SELECT docente_id " +
                                        "FROM informatica_registodocencia WHERE ano= " +
                                        iAno + " AND estabelecimento_id= '" + idEstab + "' AND grau_id= '" + degree1 + "'";

                listTeacher.AddRange(connectDB_getList(commandSql, "docente_id"));
            }

            foreach (string teacher in listTeacher)
            {
                if (listTeachers.Contains(teacher) == false)
                {
                    listTeachers.Add(teacher);
                }
            }

            writeFile("Holders of a doctorate degree in establishment " + iEstab + " in year 200" + iAno + "<p>\n" +
                      " &nbsp; The number of holders: " + listTeachers.Count + "<p>\n" +
                      " &nbsp; The set of holders: ");


            print_ListTeachers(listTeachers);
        }

        static void count_teachers_year_degree_estab(int iAno,  string degree, string iEstab)
        {
            String commandSql = "SELECT COUNT(DISTINCT docente_id) " +
                                "FROM informatica_registodocencia WHERE ano= " + iAno +
                                " AND estabelecimento_id= " +
                                "(SELECT id FROM informatica_estabelecimento WHERE designacao= '" +
                                iEstab + "' AND ano= '" + iAno + "') AND grau_id= " +
                                "(SELECT id FROM informatica_grau WHERE designacao= '"
                                + degree + "')";

            writeFile("Number of teachers with the degree " + degree + ", in " + iEstab +
                " and in the year 200" + iAno + ": " + connectDB_getList(commandSql, "docente_id")[0]);
        }

        static void count_teachers_year_degree(int iAno, string degree)
        {
            String commandSql = "SELECT COUNT(DISTINCT docente_id) " +
                                "FROM informatica_registodocencia WHERE grau_id= " +
                                "(SELECT id FROM informatica_grau WHERE designacao= '"
                                + degree + "') AND ano= " + iAno;

            writeFile("Number of teachers in the year 200" + iAno + "  with the degree " + degree + 
                ": " + connectDB_getList(commandSql, "docente_id")[0]);
        } 

        static void count_teachers_year_estab(int iAno, string iEstab)
        {
            String commandSql = "SELECT COUNT(DISTINCT docente_id) " +
                                "FROM informatica_registodocencia WHERE ano= " +
                                iAno + " AND estabelecimento_id= " +
                                "(SELECT id FROM informatica_estabelecimento WHERE designacao= '" +
                                iEstab + "' AND ano= '" + iAno + "')";

            writeFile("Number of teachers in the year 200" + iAno + " in " + iEstab + 
                ": " + connectDB_getList(commandSql, "docente_id")[0]);
        } 

        static void count_teachers_year(int iAno)
        {
            String commandSql = "SELECT COUNT(DISTINCT docente_id) " +
                                "FROM informatica_registodocencia " +
                                "WHERE ano= " + iAno;

            writeFile("Number of teachers in the higher education system in the year 200" + iAno +
                ": " + connectDB_getList(commandSql, "docente_id")[0]);

        }

        static void Main(string[] args)
        {
            if ((args.Length) < 1)
            {
                Console.WriteLine("\n" +
                "Usage: trabalhoCsharp.exe -y <year>                                  [number of teachers in the higher education system per year] \n" +
                "                          -yd <year> <degree>                        [number of teachers per degree and per year] \n" +
                "                          -ye <year> <establishment>                 [number of teachers per establishment and per year] \n" +
                "                          -yechange <year> <establishment>           [number of teachers that changed category per establishment and per year] \n" +
                "                          -yde <year> <degree> <establishment>       [number of teachers per degree, per establishment and per year] \n" +
                "                          -yAll <year> <degree> <establishment>      [Make all statistics with the same entries] \n" +
                "                          -listYE <year>                             [list of establishments per year] \n" +
                "                          -listYEchanged <year>                      [list of teachers that changed establishment per year] \n" +
                "                          -listYDholder <year> <degree>              [list of holders of a degree per year] \n" +
                "                          -listYELA <year> <establishment>           [list of personnel leaving and arriving per institution and per year] \n" +
                "                          -listYEdoctor <year> <establishment>       [list of teachers with doctorate degree per year and per establishment] \n" +
                "                          -listsAll <year> <degree> <establishment>  [Make all lists with the same entries] \n" +
                "                          -all <year> <degree> <establishment>       [Make all statistics and lists with the same entries] \n" +
                "                          -clear                                     [Clears the content of the file] \n" +
                "                          example: -y 2000 \n" +
                "                                   -all 2006 \"Licenciatura\" \"Escola Naval\" \n" +
                "                                   -y 2000 -listYDholder 2000 \"Mestrado\" ");
            }
            else
            {
                for (int i = 0; i < args.Length; i++)
                {
                    switch (args[i].ToString())
                    {
                        case ("-y"):
                            count_teachers_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            break;
                        case ("-yd"):
                            count_teachers_year_degree(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            break;
                        case ("-ye"):
                            count_teachers_year_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            break;
                        case ("-yechange"):
                            count_teachers_that_changed_category_year(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            break;
                        case ("-yde"):
                            count_teachers_year_degree_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2], args[i + 3]);
                            break;
                        case ("-yAll"):
                            count_teachers_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            count_teachers_year_degree(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            count_teachers_year_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 3]);
                            count_teachers_that_changed_category_year(Int32.Parse(args[i + 1].Substring(3, 1)), args[i +32]);
                            count_teachers_year_degree_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2], args[i + 3]);
                            break;
                        case ("-listYE"):
                            list_establishments_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            break;
                        case ("-listYEchanged"):
                            list_teachers_that_changed_establishment_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            break;
                        case ("-listYDholder"):
                            list_holders_year_degree(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            break;
                        case ("-listYELA"):
                            personnel_leaving_arriving_institution_year(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            break;
                        case ("-listYEdoctor"):
                            list_teachers_doctorateDegree_year_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            break;
                        case ("-listAll"):
                            list_establishments_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            list_teachers_that_changed_establishment_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            list_holders_year_degree(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            personnel_leaving_arriving_institution_year(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 3]);
                            list_teachers_doctorateDegree_year_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 3]);
                            break;
                        case ("-all"):
                            count_teachers_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            count_teachers_year_degree(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            count_teachers_year_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 3]);
                            count_teachers_that_changed_category_year(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 3]);
                            count_teachers_year_degree_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2], args[i + 3]);
                            list_establishments_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            list_teachers_that_changed_establishment_year(Int32.Parse(args[i + 1].Substring(3, 1)));
                            list_holders_year_degree(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 2]);
                            personnel_leaving_arriving_institution_year(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 3]);
                            list_teachers_doctorateDegree_year_estab(Int32.Parse(args[i + 1].Substring(3, 1)), args[i + 3]);
                            break;
                        case ("-clear"):
                            clearFile();
                            break;
                        default:
                            break;
                    }
                }

            }

            Console.WriteLine("prima uma tecla...");
            Console.ReadKey();
        }

    }
}